import datetime
import json

import cx_Oracle

"""SQL生成JAVA代码"""

user = "pigpos"
# user = 'dw'
passwd = "pigposdev"
# passwd = 'dwdev'
listener = 'TESTZQF.cpchina.cn/TESTZQF'
# listener = '10.240.24.192/devdw'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


if __name__ == '__main__':
    print('start')
    table_name = 'ZCN_MED_PLAN_detail'
    table_name2 = table_name.lower().capitalize()
    for word in table_name2.split('_'):
        table_name2 = table_name2.replace('_' + word.lower(), word.capitalize())
    print(table_name2)
    sql = (
        "SELECT T.COLUMN_NAME, T.COLUMN_ID, T.NULLABLE, T.DATA_TYPE || '(' || T.DATA_LENGTH || ')' as DATA_TYPE, C.COMMENTS,t.DATA_PRECISION,t.DATA_SCALE "
        "  FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C WHERE T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME AND T.TABLE_NAME = '{}' order by t.COLUMN_ID").format(
        table_name.upper())
    rs = select(sql)
    rs = json.loads(rs)
    print(type(rs))
    print(rs)
    print('\n')
    for t in rs:
        column_name = t['COLUMN_NAME']
        comments = t['COMMENTS']
        if comments is None:
            comments = ''
        data_type = t['DATA_TYPE']
        data_precision = t['DATA_PRECISION']
        print(f"{column_name},{data_type},{comments}")

    for t in rs:
        comments = t['COMMENTS']
        if comments is None:
            comments = ''
        data_type = t['DATA_TYPE']
        data_precision = t['DATA_PRECISION']
        # 根据数据字段映射java类型
        field_type = ''
        if 'DATE' in data_type or 'LocalDateTime' in data_type:
            field_type = 'Date'
            print('@JsonFormat(pattern = "yyyy/MM/dd HH:mm:ss")')
        elif 'NUMBER' in data_type:
            data_scale = t['DATA_SCALE']
            field_type = 'Integer'
            if data_scale > 0:
                field_type = 'BigDecimal'
        elif 'VARCHAR' in data_type:
            field_type = 'String'

        column_name = t['COLUMN_NAME'].lower()
        for word in column_name.split('_'):
            column_name = column_name.replace('_' + word.lower(), word.capitalize())
        # 转驼峰
        field = '@ApiModelProperty(value = "{}")\nprivate {} {};'.format(comments, field_type, column_name)
        print(field)
        print('\n')

    disconnect()
